package com.carreirati.evaluation.domain;

import java.util.List;

import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;
import org.springframework.roo.addon.tostring.RooToString;

@RooJavaBean
@RooJpaActiveRecord(versionField = "", table = "knowledge_area", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
@RooJson
public class KnowledgeArea {
	
	   public static List<com.carreirati.evaluation.domain.KnowledgeArea> fingAppraisalKnowledgeAreas(Long appraisalId) {
	    	
		   String sqlQuery = "";
			 
			sqlQuery = "SELECT knowledge_area.id , knowledge_area.description ";
			sqlQuery += " FROM appraisal_relationship INNER JOIN  question ON appraisal_relationship.question_id = question.id INNER JOIN ";
			sqlQuery += " knowledge_area ON question.knowledge_area_id =knowledge_area.id ";
			sqlQuery += " WHERE appraisal_relationship.appraisal_id = '"+ appraisalId +"' ";                  
			sqlQuery += " GROUP BY knowledge_area.description, knowledge_area.id, appraisal_relationship.appraisal_id ";
			sqlQuery += " ORDER BY  knowledge_area.description";
			 
	        return entityManager().createNativeQuery(sqlQuery, KnowledgeArea.class).getResultList();
	    }
	    
	   public static List<KnowledgeArea> findByProfile(String profileId) {
	    	String sqlString = "SELECT k.* FROM knowledge_area k INNER JOIN profile_knowledge_area pka ON k.id = pka.knowledge_area_id ";
	    		sqlString += "  WHERE pka.profile_id = '"+  profileId + "'";
	    		sqlString += " ORDER BY k.description";
	        return entityManager().createNativeQuery(sqlString, KnowledgeArea.class).getResultList();
	    }

	   
	   public static List<KnowledgeArea> findByProject(long projectId) {
	    	String sqlString = "SELECT k.* FROM knowledge_area k INNER JOIN project_knowledge_area pka ON k.id = pka.knowledge_area_id ";
	    		sqlString += "  WHERE pka.project_id = '"+  projectId + "'";
	    		sqlString += " ORDER BY k.description";
	        return entityManager().createNativeQuery(sqlString, KnowledgeArea.class).getResultList();
	    }
	    
	
	
}
